Imports DAL
Imports MyClasses
Imports System.Data.SqlClient

Public Class DProfitDetails

    Public Shared Function GetDetails(ByVal ItemID As Integer) As DataTable
        Dim strsql As String = ""
        Try
            strsql = " SELECT TOP 3 ST.ItemID,ST.BatchNO,((ST.MRP - ST.BillRate) * 100) / ST.BillRate as Margin," & _
                     " ST.MRP,ST.BillRate,ST.SalePrice1," & _
                     " (ST.MRP - ST.BillRate) as ProfitAmount,Mpvendor.VendorID,VendorName,RegisteredName,InvoiceDate" & _
                     " FROM Tstock ST" & _
                     " LEFT OUTER JOIN Mpvendor ON Mpvendor.vendorid=ST.vendorid" & _
                     " LEFT OUTER JOIN Tpurchaseinvoice ON Tpurchaseinvoice.invoiceid=ST.invoiceid" & _
                     " AND Tpurchaseinvoice.vendorid=ST.vendorid" & _
                     " WHERE ST.ItemID = " & ItemID & " AND ST.BillRate <> 0" & _
                     " ORDER BY Margin DESC"

            Return DbHelper.ExecuteDataTable(strsql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DProfitDetails", "GetDetails")
            Return Nothing
        End Try
    End Function
    'Code Start                29/12/2007

    Public Shared Function GetTopMargin(ByVal ItemID As Integer, ByVal CLRcode As String) As DataTable
        Dim strsql As String = ""
        Try
            strsql = " SELECT TOP 1 MAX(((MRP - PurchasePrice) * 100) / PurchasePrice) AS Margin," & _
                     " MRP,PurchasePrice,SalePrice1 from tstock where  purchaseprice >0 " & _
                     " AND Itemid= " & ItemID & " AND BatchNO='" & CLRcode & "' " & _
                     " GROUP BY MRP,PurchasePrice,SalePrice1"

            Return DbHelper.ExecuteDataTable(strsql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DProfitDetails", "GetTopMargin")
            Return Nothing
        End Try
    End Function
    '       30/12/2007
    Public Shared Function GetMaxMargin(ByVal ItemID As Integer) As Double
        Dim strsql As String = ""
        Try
            strsql = " SELECT TOP 1 ((ST.MRP - ST.PurchasePrice) * 100) / ST.PurchasePrice as Margin" & _
                     " FROM Tstock ST LEFT OUTER JOIN Mpvendor ON Mpvendor.vendorid=ST.vendorid " & _
                     " LEFT OUTER JOIN Tpurchaseinvoice ON Tpurchaseinvoice.invoiceid=ST.invoiceid " & _
                     " AND Tpurchaseinvoice.vendorid=ST.vendorid WHERE ST.ItemID = " & ItemID & _
                     " AND PurchasePrice <> 0 ORDER BY Margin DESC"
            Return DbHelper.ExecuteScalar(strsql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DProfitDetails", "GetMaxMargin")
            Return Nothing
        End Try
    End Function
    'Code End
End Class
